﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace SchoolManagementSystem
{
    class Database
    {
        private static Database instance = null;
        private String user = "";
        public int user_id = -1;
        private String connectionString = "";
        private SqlDataAdapter sqlDataAdapter;
        private SqlConnection sqlConnection1;

        public Database()
        { 
        
            string appPath = System.IO.Path.GetDirectoryName(Application.ExecutablePath);
            this.connectionString = "Data Source=.\\SQLExpress;"
                            + "AttachDbFilename=\"" + appPath + "\\DB\\alwardah.mdf\";"
                            + "Integrated Security=True;"
                            + "User Instance=True";
         

          
            
            //this.connectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=" + appPath + "\\alwardah.mdf;Integrated Security=True;User Instance=True";

            //this.connectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=D:\\_project\\_tk_alwardah\\_revisi2\\SchoolManagementSystem\\alwardah.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";

            // this.connectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=D:\\_project\\_tk_alwardah\\_revisi2\\SchoolManagementSystem\\alwardah.mdf;Integrated Security=True;User Instance=True";

            // this.connectionString = "Data Source=192.168.1.141\\SQLEXPRESS;Database=ALWARDAH;User ID=sa;Password=1234;Trusted_Connection=True;";
        }

        public static Database getInstance()
        {
            if (instance == null)
            {
                instance = new Database();
            }
            return instance;
        }

        public DataTable query(String query)
        {
            return queryDatabase(query, true);
        }

        public void nonquery(String query)
        {
            nonqueryDatabase(query, true);
        }

        public DataTable tes(String query)
        {
            return queryDatabase(query, true);
        }

        public void logSistem(String action)
        {
            DataTable dTable = queryDatabase("select * from Log order by time asc", false);

            DataRow dataRow = dTable.NewRow();
            dataRow[0] = DateTime.UtcNow;
            dataRow[1] = user;
            dataRow[2] = action;
            dTable.Rows.Add(dataRow);
            SqlCommandBuilder sqlCommandBuilder = new SqlCommandBuilder(sqlDataAdapter);
            sqlDataAdapter.Update(dTable);
            closeDatabase(sqlConnection1);
        }

        public Boolean checkLoginID(String user, String pass)
        {
            String query = "select * from account where (user_name='" + user + "') AND (password='" + pass + "')";
            DataTable dt = queryDatabase(query, true);
            if (dt.Rows.Count == 1)
            {
                this.user = dt.Rows[0].ItemArray.GetValue(0).ToString();
                this.user_id = (int)dt.Rows[0].ItemArray[0];
                return true;
            }
            return false;
            
             
            /* 
            String query = "select * from account";
            DataTable dt = queryDatabase(query, true);

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                string buffer = "";
                buffer = dt.Rows[i].ItemArray[0].ToString();
               
            }


            return false;
             * */ 
            
        }

        private DataTable queryDatabase(String query, Boolean closeDB)
        {
            DataTable dataTable = new DataTable();
            
            sqlConnection1 = connectDatabase();
            sqlDataAdapter = new SqlDataAdapter(query, sqlConnection1);
            sqlDataAdapter.Fill(dataTable);
            if (closeDB)
            {
                closeDatabase(sqlConnection1);
            }

            return dataTable;
        }

        private void nonqueryDatabase(String query, Boolean closeDB)
        {
            sqlConnection1 = connectDatabase();
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandText = query;
            sqlCommand.Connection = sqlConnection1;
            sqlCommand.ExecuteNonQuery();
            if (closeDB)
            {
                closeDatabase(sqlConnection1);
            }
        }

        private SqlConnection connectDatabase()
        {
            SqlConnection sqlConnection = new SqlConnection();
            sqlConnection.ConnectionString = connectionString;

            try
            {
                sqlConnection.Open();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error Connecting to Database" + ex);
            }

            return sqlConnection;
        }

        private void closeDatabase(SqlConnection sqlConnection)
        {
            try
            {
                sqlConnection.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error Closing Database" + ex);
            }
        }

        public DataTable accountAdd(String account, String username, String password, String contact, String type)
        {
            DataTable dTable = new DataTable();
            int last_accid = -1;
            string accid = "";

            dTable = queryDatabase("select * from Account", false);

            for (int i = 0; i < dTable.Rows.Count; i++)
            {
                string buffer = "";
                buffer = dTable.Rows[i].ItemArray[0].ToString();
                int num = int.Parse(buffer);

                if (num > last_accid)
                    last_accid = num;
            }
            
            accid += (last_accid + 1);

            DataRow dataRow = dTable.NewRow();
            dataRow[0] = accid;
            dataRow[1] = account;
            dataRow[2] = username;
            dataRow[3] = password;
            dataRow[4] = contact;
            dataRow[5] = type;
            dTable.Rows.Add(dataRow);
            SqlCommandBuilder sqlCommandBuilder = new SqlCommandBuilder(sqlDataAdapter);
            sqlDataAdapter.Update(dTable);
            closeDatabase(sqlConnection1);

            return dTable;
        }

        public DataTable accountGetDataByNameAndUsername(string name, string username)
        {
            string query = "select * from account where (user_name='" + username + "') AND (acc_name='" + name + "')";
            return queryDatabase(query, true);
        }

        public void updateDB(DataTable dt)
        {
            sqlConnection1 = connectDatabase();
            SqlCommandBuilder sqlCommandBuilder = new SqlCommandBuilder(sqlDataAdapter);
            sqlDataAdapter.Update(dt);
            closeDatabase(sqlConnection1);
        }

        public DataTable getGroups()
        {
            return this.query("SELECT * FROM [Group]");
        }

        public void insert(DataTable dt)
        {
            sqlConnection1 = connectDatabase();
            SqlCommandBuilder sqlCommandBuilder = new SqlCommandBuilder(sqlDataAdapter);
            sqlDataAdapter.Update(dt);
            closeDatabase(sqlConnection1);
        }

        public void update(DataTable dt)
        {
            this.insert(dt);
        }
    }
}
